/*
	EXEC sp_cool_requested_schedule_dates_change_log_report '0','0','0','0','','0','0','XLREPORT','0'
*/

alter PROC sp_cool_requested_schedule_dates_change_log_report

@CUSTOMERIDS	VARCHAR(200),
@PRODUCTIDS	VARCHAR(200),
@SONUMBER	VARCHAR(50),
@STATUS		VARCHAR(50),
@ORDERTYPE	VARCHAR(10),
@FROMDATE	VARCHAR(50),
@TODATE		VARCHAR(50),
@REPORTTYPE	VARCHAR(20),
@DEFAULTPROMPT	VARCHAR(50)

AS
BEGIN
	CREATE TABLE #CHANGE_LOG_TABLE
		( [RequestedDate Rev1] DATETIME,[RequestedDate Rev2] DATETIME,[RequestedDate Rev3] DATETIME,
		  [RequestedDate Rev4] DATETIME,[RequestedDate Rev5] DATETIME,
		  [ScheduleDate Rev1] DATETIME,[ScheduleDate Rev2] DATETIME,[ScheduleDate Rev3] DATETIME,
		  [ScheduleDate Rev4] DATETIME,[ScheduleDate Rev5] DATETIME,
		  [Line Item #] INT, [Current Requested Date] DATETIME,[Current Schedule Date]DATETIME,
		  [Status] VARCHAR(50),[Sales Order #] VARCHAR(50),[Cust PO #] VARCHAR(50),[Booking Date] DATETIME,
		  [Customer] VARCHAR(50),[Part Number] VARCHAR(50),[Mkt Part Number] VARCHAR(50),[Latest Req Delta] int,[Req Delta] int, [Remaining Qty] INT, 
		  [LATEST CONFIRMED DATE] DATETIME, [ACT SHIP DATE] DATETIME, [Ship Req No] VARCHAR(50))

	DECLARE @SQLSTRING	VARCHAR(4000)

	SET @SQLSTRING = N'
		INSERT INTO #CHANGE_LOG_TABLE
		SELECT
			CRD_REV1,CRD_REV2,CRD_REV3,CRD_REV4,CRD_REV5,
			SCHEDULE_REV1,SCHEDULE_REV2,SCHEDULE_REV3,SCHEDULE_REV4,SCHEDULE_REV5,
			COOL_LINE_ITEM_NO,COOL_REQUESTED_DATE,ISNULL(COOL_REVISED_DATE,COOL_SCHEDULE_DATE),
			COOL_STATUS,SO.SALESORDER_NO,SO_CUSTOMER_PO_NUMBER,SO_BOOKING_DATE,C_NAME,
			PART_NUMBER,P_MARKETING_PART_NUMBER,DATEDIFF(DD,SO_BOOKING_DATE,COOL_REQUESTED_DATE ),
			DATEDIFF(DD,SO_BOOKING_DATE,isnull(CRD_REV1,COOL_REQUESTED_DATE)),
			/*(case 
				when CRD_REV1 is not null and len(CRD_REV1) > 0 then DATEDIFF(DD,SO_BOOKING_DATE,isnull(CRD_REV1,COOL_REQUESTED_DATE))
				else null
			end)*/
			(COOL.COOL_QTY - ( SELECT ISNULL(SUM(CSLIT_QTY_SHIPPED),0) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID = COOL.LINEITEM_ID AND CSLIT_STATUS = ''SHIPPED'' )),
			(SELECT MAX(CST_CONFIRMED_ON) FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT
				WHERE CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID AND CST.CST_STATUS = ''SHIPPED''
				AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID) AS [LATEST CONFIRMED DATE],
			(SELECT TOP 1 CST_SHIP_DATE FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT
				WHERE CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID AND CST.CST_STATUS = ''SHIPPED''
				AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND CST.CST_CONFIRMED_ON = (SELECT MAX(CST_CONFIRMED_ON) FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT
				WHERE CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID AND CST.CST_STATUS = ''SHIPPED'' AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID)) AS [ACT SHIP DATE], COOL.ShipReqNum
		FROM 
			COOL_REQUEST_SCHEDULE_DATES_CHANGE_LOG CRSD,CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO, CUSTOMER C, PRODUCT P
		WHERE 
			CRSD.LINEITEM_ID = COOL.LINEITEM_ID 
			AND CRSD.SALESORDER_ID = SO.SALESORDER_ID 
			AND SO.CUSTOMER_ID = C.CUSTOMER_ID 
			AND COOL.PRODUCT_ID = P.PRODUCT_ID '

		IF @CUSTOMERIDS IS NOT NULL AND @CUSTOMERIDS <> '0'
	 	BEGIN
	 		SET @SQLSTRING = @SQLSTRING + ' AND SO.Customer_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERIDS +'''))' 
	 	END
	 	IF @PRODUCTIDS IS NOT NULL AND @PRODUCTIDS <> '0'
	 	BEGIN
	 		SET @SQLSTRING = @SQLSTRING + ' AND COOL.Product_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTIDS +'''))'
	 	END
	 	IF @SONUMBER IS NOT NULL AND @SONUMBER <> '0'
	 	BEGIN
	 		SET @SQLSTRING = @SQLSTRING + ' AND SO.SalesOrder_No = '''+ @SONUMBER+''''
	 	END
		IF @STATUS IS NOT NULL AND @STATUS <> '0'
	 	BEGIN
	 		SET @SQLSTRING = @SQLSTRING + ' AND COOL_STATUS = '''+ @STATUS+''''
	 	END
	 
	 	IF ( @ORDERTYPE IS NOT NULL AND len(ltrim(rtrim(@ORDERTYPE)))  > 0 )
	 	BEGIN
	 		SET @SQLSTRING = @SQLSTRING + ' AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	 	END
	 		 
	 	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	 	BEGIN
	 		SET @SQLSTRING = @SQLSTRING +' AND CRSD.TIMESTAMP >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	 	END
	 	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	 	BEGIN
	 		SET @SQLSTRING = @SQLSTRING + +' AND CRSD.TIMESTAMP <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	 	END

	--PRINT '@SQLSTRING :- '+@SQLSTRING
	EXEC (@SQLSTRING)

	IF @REPORTTYPE = 'XLREPORT'
	BEGIN
		SELECT 
			[Customer],[Sales Order #],[Line Item #], [Ship Req No], [Cust PO #],[Part Number],
			[Booking Date],[Current Requested Date],
			[RequestedDate Rev1],[RequestedDate Rev2],[RequestedDate Rev3],[RequestedDate Rev4],[Req Delta],[Latest Req Delta],--,[RequestedDate Rev5],
		  	[Current Schedule Date],[ScheduleDate Rev1],[ScheduleDate Rev2],[ScheduleDate Rev3],[ScheduleDate Rev4],--[ScheduleDate Rev5],
		  	[Status], [Remaining Qty], [LATEST CONFIRMED DATE], [ACT SHIP DATE]
		FROM
			#CHANGE_LOG_TABLE
		ORDER BY 
			[Customer]
	END
	ELSE
	BEGIN
		SELECT 
			[RequestedDate Rev1],[RequestedDate Rev2],[RequestedDate Rev3],[RequestedDate Rev4],[RequestedDate Rev5],
		  	--[ScheduleDate Rev1],[ScheduleDate Rev2],[ScheduleDate Rev3],[ScheduleDate Rev4],[ScheduleDate Rev5],
		  	[Line Item #], [Current Requested Date],[Current Schedule Date],
		  	[Status],[Sales Order #],[Cust PO #],[Customer],[Part Number],[Mkt Part Number],
			[Booking Date],[Req Delta],[Latest Req Delta]
		FROM
			#CHANGE_LOG_TABLE
		ORDER BY 
			[Customer]
	END 
END